from django.shortcuts import render, redirect
import MySQLdb
from MySQLdb.cursors import DictCursor
from .models import Student

# 创建全局数据库连接
conn = MySQLdb.connect(host="127.0.0.1",
                       user="root",
                       passwd="root",
                       db="Django",
                       charset='utf8',
                       cursorclass=DictCursor)


def index(request):
    with conn.cursor() as cursor:
        cursor.execute("SELECT id, student_no, student_name, student_city FROM app01_student")
        students = cursor.fetchall()

        # 查询每个城市的学生数量
        cursor.execute("SELECT student_city, COUNT(*) as count FROM app01_student GROUP BY student_city")
        city_counts = cursor.fetchall()

    return render(request, 'student/index.html', {'students': students, 'city_counts': city_counts})


def add(request):
    if request.method == 'GET':
        return render(request, 'student/add.html')
    else:
        student_no = request.POST.get('student_no', '')
        student_name = request.POST.get('student_name', '')
        student_city = request.POST.get('student_city', '')
        cursor = conn.cursor()
        cursor.execute("INSERT INTO app01_student (student_no, student_name, student_city) VALUES (%s, %s, %s)",
                       [student_no, student_name, student_city])
        conn.commit()
        return redirect('index')  # 重定向到查看页面

def edit(request):
    if request.method == 'GET':
        id = request.GET.get("id")
        cursor = conn.cursor()
        cursor.execute("SELECT id, student_no, student_name, student_city FROM app01_student WHERE id = %s", [id])
        student = cursor.fetchone()
        return render(request, 'student/edit.html', {'student': student})
    else:
        id = request.POST.get("id")
        student_no = request.POST.get('student_no', '')
        student_name = request.POST.get('student_name', '')
        student_city = request.POST.get('student_city', '')
        cursor = conn.cursor()
        cursor.execute("UPDATE app01_student SET student_no=%s, student_name=%s, student_city=%s WHERE id =%s",
                       [student_no, student_name, student_city, id])
        conn.commit()
        return redirect('index')  # 重定向到查看页面

def delete(request):
    id = request.GET.get("id")
    cursor = conn.cursor()
    cursor.execute("DELETE FROM app01_student WHERE id = %s", [id])
    conn.commit()
    return redirect('index')  # 重定向到查看页面
